package vn.com.demand.lux.criteria;

import java.sql.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import vn.com.demand.lux.model.InterestSummaryBean;
import vn.com.demand.util.DpUtils;

public class UpdateWarrantyExpenseCriteria {

	private String installmentLedger;
	private String defferedRevenueLedger;
	private Date fromDate;
	private Date toDate;

	public String getInstallmentLedger() {
		return installmentLedger;
	}

	public void setInstallmentLedger(String installmentLedger) {
		this.installmentLedger = installmentLedger;
	}

	public String getDefferedRevenueLedger() {
		return defferedRevenueLedger;
	}

	public void setDefferedRevenueLedger(String defferedRevenueLedger) {
		this.defferedRevenueLedger = defferedRevenueLedger;
	}

	public Date getFromDate() {
		return fromDate;
	}

	public void setFromDate(Date fromDate) {
		this.fromDate = fromDate;
	}

	public Date getToDate() {
		return toDate;
	}

	public void setToDate(Date toDate) {
		this.toDate = toDate;
	}
	
	@SuppressWarnings("unchecked")
	public List<InterestSummaryBean> search() {
		try {
			String sql = "SELECT * FROM (SELECT ContractNumber, CustomerCode, CustomerName, CostCenter, InstallmentAmount, InterestAmount, " +
						 "CollectedAmount, ForwardedAmount, InterestToForward, InterestToForward - ForwardedAmount AS InterestTobeForward" +
						 " FROM (SELECT ContractNumber, MAX(CustomerCode) AS CustomerCode, MAX(CustomerName) AS CustomerName, MAX(CostCenter) " +
						 " AS CostCenter, SUM(InstallmentAmount) AS InstallmentAmount, SUM(InterestAmount) AS InterestAmount, SUM(CollectedAmount) " +
						 " AS CollectedAmount, SUM(ForwardedAmount) AS ForwardedAmount, CASE WHEN SUM(InstallmentAmount) <> 0 " +
						 " THEN round(SUM(InterestAmount) * SUM(CollectedAmount) / SUM(InstallmentAmount), 2) ELSE SUM(ForwardedAmount)" +
						 " END AS InterestToForward FROM (SELECT p.ProjectNr AS ContractNumber, c.cmp_code AS CustomerCode, c.cmp_name " +
						 " AS CustomerName, CASE WHEN ltrim(rtrim(g.reknr)) = '131201' AND g.bdr_val > 0 AND g.transsubtype IN ('K', 'C') " +
						 " THEN bdr_val ELSE 0 END AS InstallmentAmount, CASE WHEN ltrim(rtrim(g.reknr)) = '338700' AND " +
						 " g.bdr_val < 0 THEN - bdr_val ELSE 0 END AS InterestAmount, CASE WHEN ltrim(rtrim(g.reknr)) = '131201' AND" +
						 " g.bdr_val < 0 AND g.transsubtype IN ('Z', 'Y') THEN - bdr_val ELSE 0 END AS CollectedAmount," +
						 " CASE WHEN ltrim(rtrim(g.reknr)) = '338700' AND g.bdr_val > 0 THEN bdr_val ELSE 0 END AS ForwardedAmount," +
						 " p.CostCenter FROM dbo.PRProject p INNER JOIN dbo.gbkmut g ON p.ProjectNr = g.project LEFT OUTER JOIN" +
						 " dbo.cicmpy c ON c.cmp_wwn = p.IDCustomer AND p.IDCustomer IS NOT NULL WHERE (NOT (g.transtype IN ('V', 'B')) " +
						 " AND (g.datum <= { d '2008-01-31' }))) g GROUP BY ContractNumber) g) g" +
						 " WHERE (InterestTobeForward > 0)";
			
			sql = sql.replaceAll("131201", installmentLedger);
			sql = sql.replaceAll("338700", defferedRevenueLedger);
			sql = sql.replaceAll("2008-01-31", "" + toDate);
//			System.out.println("SQL: " +sql);
			QueryRunner runner = new QueryRunner(DpUtils.getDataSource());
			return (List<InterestSummaryBean>) runner.query(sql,
					new BeanListHandler(InterestSummaryBean.class));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
